Before executing a structured query language (SQL) statement, a database may create a detailed, comprehensive plan for executing the SQL statement. The plan may be referred to as an execution plan. How the data is retrieved can significantly impact how long it will take to execute an SQL statement. How the SQL statement is executed can also significantly impact the performance of an SQL statement. SQL performance may be measured in terms of system resources consumed and total response time. The system resources may include, for example, central processing unit (CPU) time, input/output (I/O) requests, and so on. An execution plan may include, among other things, a strategy for retrieving data. Conventionally, if the decision about how to retrieve data has been guided, it has been guided by a database component referred to as a query optimizer. Query optimizers have employed different optimization strategies including, for example, a cost based optimizer (CBO), and a rule based optimizer (RBO). An RBO chooses an execution plan for an SQL statement based on a rules-based analysis of how the SQL statement is written. A CBO chooses an execution plan for an SQL statement based on data statistics. A CBO may check several possible execution plans, compute a cost for the different execution plans, and select the one with the lowest cost. The cost may be computed with respect to system resources (e.g., memory, processor cycles). However, when new objects are created, when the amount of data in the database changes, when the characteristics of data in the database changes, and when other actions occur, the statistics may no longer represent the real state of the database and thus the decision of a CBO may be seriously impaired. Similarly, without information concerning characteristics of the data, the ability of an RBO to select a best execution plan may be limited.
SQL performance is a critical component of the overall performance of a database system. However, SQL performance has conventionally been one of the least manageable components of a database system. An execution plan may be selected to control how an SQL statement runs. On different runs of the SQL statement, different execution plans may be selected. Although an execution plan for an SQL statement may have previously been verified a query optimizer may not be constrained to use only that selected, verified execution plan and thus may change to a non-verified execution plan. Thus, a query optimizer may choose different execution plans for different runs of an SQL statement. The execution plan chosen may have never been tested. Additionally, underlying data and/or assumptions may have changed, which may cause even previously verified execution plans to perform poorly in the changed conditions, leading to undesirable performances at unpredictable times. Conventionally, if performance analysis has been performed at all, it has included significant manual activity that reacts to execution issues.
One conventional SQL tuning logic implements an automated tuning process that examines statements in isolation and performs two kinds of analysis. A first type of analysis is a rules-based analysis. The rules-based analysis seeks out general flaws in the way an SQL statement is written. The rules-based analysis may also seek out general flaws in how a schema is designed. Either type of general flaw may prevent desirable (e.g., optimal) execution plans from being available to a query optimizer. A second type of analysis involves a background investigation into the database system to attempt to discover flawed or missing statistics that may mislead a query optimizer. A mislead query optimizer may yield poor performance. Although valuable, both analyses associated with the conventional SQL tuning logic are limited by the costing model employed by the tuning logic. The limitations arise, at least in part, because cost-based query optimization is an inexact science that relies on assumptions that may be made on incorrect and/or stale statistics. Important facts about a plan may be unknown to a cost based tuning logic, which limits the capabilities of a purely cost-based automatic SQL tuner. Similar issues may arise with other conventional single-approach optimizers.
A conventional CBO may consider a large number (e.g., 1000) of possibilities for executing an SQL statement. The CBO may cost each of the possibilities and then select the lowest cost possibility. While providing a good solution for the possibilities it considers, the CBO may not consider a complete set of execution plans. Considering a complete set of plans could require more than an allowed amount of processing time. Similarly, a conventional RBO may consider a large number of possibilities for executing an SQL statement. Once again, while valuable, the analysis may be incomplete.